package dao.impl;

import bean.Role;
import bean.Users;
import dao.UsersDao;
import utils.DruidUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UsersDaoImpl extends DruidUtils implements UsersDao {
    @Override
    public Users getUser(String loginname, String password) {
        Users user = new Users();
        try {
            String sql = "select * from users where loginname = ? and password = ?";
            ArrayList list = new ArrayList();
            list.add(loginname);
            list.add(password);
            rs = query(sql, list);

            if (rs.next()) {
                user.setLoginName(rs.getString("loginname"));
                user.setUseid(rs.getInt("useid"));
                user.setRoleid(rs.getInt("roleid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeall();
        }
        return user;
    }

    @Override
    public List<Users> getUserList(int index, int pagesize) {
        ArrayList<Users> users = null;
        try {
            String sql = "select *  from users u,role r where u.roleid = r.roleid limit ?,?";
            ArrayList list = new ArrayList();
            list.add((index - 1) * pagesize);
            list.add(pagesize);
            users = new ArrayList<>();
            rs = query(sql, list);
            while (rs.next()) {
                Users user = new Users();
                user.setUseid(rs.getInt("useid"));
                user.setUsersex(rs.getInt("usersex"));
                user.setLoginName(rs.getString("loginname"));
                user.setRealname(rs.getString("realname"));
                user.setPassword(rs.getString("password"));
                user.setUseremail(rs.getString("useremail"));
                user.setUserphone(rs.getString("userphone"));
                user.setUseraddress(rs.getString("useraddress"));
                user.setUseridnumber(rs.getString("useridnumber"));
                user.setUserintroduction(rs.getString("userintroduction"));
                Role role = new Role();
                role.setRoleid(rs.getInt("roleid"));
                role.setRolename(rs.getString("rolename"));
                role.setRolestate(rs.getInt("rolestate"));
                user.setRole(role);
                users.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeall();
        }
        return users;
    }

    @Override
    public int getTotal() {
        String sql = "select count(*)  from users u,role r where u.roleid = r.roleid ";
        int total = 0;
        try {
            ArrayList list = new ArrayList();
            rs = query(sql, null);
            total = 0;
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeall();
        }
        return total;
    }

    @Override
    public int addUser(Users u) {
        String sql = "insert into users values(?,?,?,?,?,?,?,?,?,?,?)";
        ArrayList list = new ArrayList();
        list.add(null);
        list.add(u.getLoginName());
        list.add(u.getPassword());
        list.add(u.getRealname());
        list.add(u.getUsersex());
        list.add(u.getUseremail());
        list.add(u.getUserphone());
        list.add(u.getUseraddress());
        list.add(u.getUseridnumber());
        list.add(u.getUserintroduction());
        list.add(u.getRoleid());
        k = update(sql, list);
        closeall();
        return k;
    }

    @Override
    public List<Role> getRoleList() {
        ArrayList list = null;
        try {
            String sql = "select * from role";
            list = new ArrayList();

            rs = query(sql, null);
            while (rs.next()) {
                Role role = new Role();
                role.setRoleid(rs.getInt("roleid"));
                role.setRolename(rs.getString("rolename"));
                role.setRolestate(rs.getInt("rolestate"));
                list.add(role);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeall();
        }
        return list;
    }

    @Override
    public Users findUserById(int userid) {
        String sql = "select * from users where useid =?";
        ArrayList list = new ArrayList();
        list.add(userid);
        rs=query(sql,list);
        Users user = new Users();
        try {
            while(rs.next()) {
                user.setUseid(rs.getInt("useid"));
                user.setUsersex(rs.getInt("usersex"));
                user.setLoginName(rs.getString("loginname"));
                user.setRealname(rs.getString("realname"));
                user.setPassword(rs.getString("password"));
                user.setUseremail(rs.getString("useremail"));
                user.setUserphone(rs.getString("userphone"));
                user.setUseraddress(rs.getString("useraddress"));
                user.setUseridnumber(rs.getString("useridnumber"));
                user.setUserintroduction(rs.getString("userintroduction"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeall();
        }
        return user;
    }

    @Override
    public int updateUserById(Users u) {
        String sql = "update users set loginname=?,password=?,realname=?,usersex=?,useremail=?," +
                "userphone=?,useraddress=?,useridnumber=?,userintroduction=?,roleid=? where useid=?";
        ArrayList list = new ArrayList();

        list.add(u.getLoginName());
        list.add(u.getPassword());
        list.add(u.getRealname());
        list.add(u.getUsersex());
        list.add(u.getUseremail());
        list.add(u.getUserphone());
        list.add(u.getUseraddress());
        list.add(u.getUseridnumber());
        list.add(u.getUserintroduction());
        list.add(u.getRoleid());
        list.add(u.getUseid());
        k = update(sql, list);
        closeall();
        return k;
    }

    @Override
    public int deleteUserById(int userid) {
        String sql = "delete from users where useid =?";
        ArrayList list = new ArrayList();
        list.add(userid);
        k=update(sql,list);
        return k;
    }

}
